<!doctype html>
<html lang="zh-cn">
<head>

    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">

    <title>使用explain分析sql语句 | lsy&#39;blog</title>
    <meta property="og:title" content="使用explain分析sql语句 - lsy&#39;blog">
    <meta property="og:type" content="article">
        
    <meta property="article:published_time" content='2020-04-06T20:22:40&#43;08:00'>
        
        
    <meta property="article:modified_time" content='2020-04-06T20:22:40&#43;08:00'>
        
    <meta name="Keywords" content="golang,go语言,go语言笔记,lsy,java,博客,软件架构">
    <meta name="description" content="使用explain分析sql语句">
        
    <meta name="author" content="lsy">
    <meta property="og:url" content="http://kklt1996.gitee.io/blog/post/mysql/2020-04-06-%E4%BD%BF%E7%94%A8explain%E5%88%86%E6%9E%90sql%E8%AF%AD%E5%8F%A5/">
    <link rel="shortcut icon" href="/favicon.ico" type="image/x-icon">

    <link rel="stylesheet" href='/blog/css/normalize.css'>
    <link rel="stylesheet" href='/blog/css/style.css'>
    <script type="text/javascript" src="//cdnjs.cloudflare.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>

    


    
    
        <link rel="stylesheet" href='/css/douban.css'>
    
        <link rel="stylesheet" href='/css/other.css'>
    
</head>


<body>
    <header id="header" class="clearfix">
    <div class="container">
        <div class="col-group">
            <div class="site-name ">
                
                    <a id="logo" href="http://kklt1996.gitee.io/blog/">
                        lsy&#39;blog
                    </a>
                
                <p class="description">专注于Java、Go语言(golang)、软件架构</p>
            </div>
            <div>
                <nav id="nav-menu" class="clearfix">
                    <a class="current" href="http://kklt1996.gitee.io/blog/">首页</a>
                    
                    <a  href="http://kklt1996.gitee.io/blog/archives/" title="归档">归档</a>
                    
                    <a  href="http://kklt1996.gitee.io/blog/categories/" title="分类">分类</a>
                    
                    <a  href="http://kklt1996.gitee.io/blog/tags/" title="标签">标签</a>
                    
                </nav>
            </div>
        </div>
    </div>
</header>

    <div id="body">
        <div class="container">
            <div class="col-group">

                <div class="col-8" id="main">
                    
<div class="res-cons">
    <style type="text/css">
    .post-toc {
        position: fixed;
        width: 200px;
        margin-left: -210px;
        padding: 5px 10px;
        font-family: Athelas, STHeiti, Microsoft Yahei, serif;
        font-size: 12px;
        border: 1px solid rgba(0, 0, 0, .07);
        border-radius: 5px;
        background-color: rgba(255, 255, 255, 0.98);
        background-clip: padding-box;
        -webkit-box-shadow: 1px 1px 2px rgba(0, 0, 0, .125);
        box-shadow: 1px 1px 2px rgba(0, 0, 0, .125);
        word-wrap: break-word;
        white-space: nowrap;
        -webkit-box-sizing: border-box;
        box-sizing: border-box;
        z-index: 999;
        cursor: pointer;
        max-height: 70%;
        overflow-y: auto;
        overflow-x: hidden;
    }

    .post-toc .post-toc-title {
        width: 100%;
        margin: 0 auto;
        font-size: 20px;
        font-weight: 400;
        text-transform: uppercase;
        text-align: center;
    }

    .post-toc .post-toc-content {
        font-size: 15px;
    }

    .post-toc .post-toc-content>nav>ul {
        margin: 10px 0;
    }

    .post-toc .post-toc-content ul {
        padding-left: 20px;
        list-style: square;
        margin: 0.5em;
        line-height: 1.8em;
    }

    .post-toc .post-toc-content ul ul {
        padding-left: 15px;
        display: none;
    }

    @media print,
    screen and (max-width:1057px) {
        .post-toc {
            display: none;
        }
    }
</style>
<div class="post-toc" style="position: absolute; top: 188px;">
    <h2 class="post-toc-title">文章目录</h2>
    <div class="post-toc-content">
        <nav id="TableOfContents">
  <ul>
    <li><a href="#模拟数据">模拟数据</a></li>
    <li><a href="#执行explain">执行explain</a></li>
    <li><a href="#explain输出字段含义">explain输出字段含义</a></li>
    <li><a href="#explain中select_type">explain中select_type</a></li>
    <li><a href="#explain中type">explain中type</a></li>
    <li><a href="#explain中extra">explain中Extra</a></li>
    <li><a href="#sql分析的其他方式">sql分析的其他方式</a></li>
  </ul>
</nav>
    </div>
</div>
<script type="text/javascript">
    $(document).ready(function () {
        var postToc = $(".post-toc");
        if (postToc.length) {
            var leftPos = $("#main").offset().left;
            if(leftPos<220){
                postToc.css({"width":leftPos-10,"margin-left":(0-leftPos)})
            }

            var t = postToc.offset().top - 20,
                a = {
                    start: {
                        position: "absolute",
                        top: t
                    },
                    process: {
                        position: "fixed",
                        top: 20
                    },
                };
            $(window).scroll(function () {
                var e = $(window).scrollTop();
                e < t ? postToc.css(a.start) : postToc.css(a.process)
            })
        }
    })
</script>
    <article class="post">
        <header>
            <h1 class="post-title">使用explain分析sql语句</h1>
        </header>
        <date class="post-meta meta-date">
            2020年4月6日
        </date>
        
        <div class="post-meta">
            <span>|</span>
            
            <span class="meta-category"><a href='http://kklt1996.gitee.io/categories/mysql'>mysql</a></span>
            
        </div>
        
        
        <div class="post-meta">
            <span id="busuanzi_container_page_pv">|<span id="busuanzi_value_page_pv"></span><span>
                    阅读</span></span>
        </div>
        
        
        <div class="clear" style="display: none">
            <div class="toc-article">
                <div class="toc-title">文章目录</div>
            </div>
        </div>
        
        <div class="post-content">
            <p>mysql的<code>explain</code>语句能查看sql的执行计划,根据执行计划能分析写的sql的执行效率.是优化sql的常用工具之一.</p>
<h2 id="模拟数据">模拟数据</h2>
<p>首先模拟订单数据</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4;">
<table style="border-spacing:0;padding:0;margin:0;border:0;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre tabindex="0" style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 1
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 2
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 3
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 4
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 5
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 6
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 7
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 8
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 9
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">10
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">11
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">12
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">13
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">14
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">15
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">16
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">17
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">18
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">19
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">20
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">21
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">22
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">23
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">24
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">25
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre tabindex="0" style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-sql" data-lang="sql"><span style="display:flex;"><span><span style="color:#000;font-weight:bold">drop</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">table</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">if</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">exists</span><span style="color:#bbb"> </span>tbl_order_info;<span style="color:#bbb">
</span></span></span><span style="display:flex;"><span><span style="color:#bbb"></span><span style="color:#000;font-weight:bold">CREATE</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">TABLE</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">`</span>tbl_order_info<span style="color:#000;font-weight:bold">`</span><span style="color:#bbb"> </span>(<span style="color:#bbb">
</span></span></span><span style="display:flex;"><span><span style="color:#bbb">                      </span><span style="color:#000;font-weight:bold">`</span>id<span style="color:#000;font-weight:bold">`</span><span style="color:#bbb"> </span><span style="color:#0086b3">int</span>(<span style="color:#099">11</span>)<span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">NOT</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">NULL</span><span style="color:#bbb"> </span>auto_increment,<span style="color:#bbb">
</span></span></span><span style="display:flex;"><span><span style="color:#bbb">                      </span><span style="color:#000;font-weight:bold">`</span>amount<span style="color:#000;font-weight:bold">`</span><span style="color:#bbb"> </span><span style="color:#0086b3">decimal</span>(<span style="color:#099">12</span>,<span style="color:#099">2</span>)<span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">DEFAULT</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">NULL</span>,<span style="color:#bbb">
</span></span></span><span style="display:flex;"><span><span style="color:#bbb">                      </span><span style="color:#000;font-weight:bold">`</span>user_no<span style="color:#000;font-weight:bold">`</span><span style="color:#bbb"> </span><span style="color:#0086b3">varchar</span>(<span style="color:#099">32</span>)<span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">DEFAULT</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">NULL</span>,<span style="color:#bbb">
</span></span></span><span style="display:flex;"><span><span style="color:#bbb">                      </span><span style="color:#000;font-weight:bold">`</span>create_time<span style="color:#000;font-weight:bold">`</span><span style="color:#bbb"> </span>datetime<span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">NOT</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">NULL</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">DEFAULT</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">CURRENT_TIMESTAMP</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">COMMENT</span><span style="color:#bbb"> </span><span style="color:#d14">&#39;记录创建时间&#39;</span>,<span style="color:#bbb">
</span></span></span><span style="display:flex;"><span><span style="color:#bbb">                      </span><span style="color:#000;font-weight:bold">`</span>update_time<span style="color:#000;font-weight:bold">`</span><span style="color:#bbb"> </span>datetime<span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">NOT</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">NULL</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">DEFAULT</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">CURRENT_TIMESTAMP</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">ON</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">UPDATE</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">CURRENT_TIMESTAMP</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">COMMENT</span><span style="color:#bbb"> </span><span style="color:#d14">&#39;记录更新时间&#39;</span>,<span style="color:#bbb">
</span></span></span><span style="display:flex;"><span><span style="color:#bbb">                      </span><span style="color:#000;font-weight:bold">PRIMARY</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">KEY</span><span style="color:#bbb"> </span>(<span style="color:#000;font-weight:bold">`</span>id<span style="color:#000;font-weight:bold">`</span>),<span style="color:#bbb">
</span></span></span><span style="display:flex;"><span><span style="color:#bbb">                      </span><span style="color:#000;font-weight:bold">KEY</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">`</span>idx_user_no<span style="color:#000;font-weight:bold">`</span><span style="color:#bbb"> </span>(<span style="color:#000;font-weight:bold">`</span>user_no<span style="color:#000;font-weight:bold">`</span>),<span style="color:#bbb">
</span></span></span><span style="display:flex;"><span><span style="color:#bbb">                      </span><span style="color:#000;font-weight:bold">KEY</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">`</span>idx_amount<span style="color:#000;font-weight:bold">`</span><span style="color:#bbb"> </span>(<span style="color:#000;font-weight:bold">`</span>amount<span style="color:#000;font-weight:bold">`</span>)<span style="color:#bbb">
</span></span></span><span style="display:flex;"><span><span style="color:#bbb"></span>)<span style="color:#bbb"> </span>ENGINE<span style="color:#000;font-weight:bold">=</span>InnoDB<span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">DEFAULT</span><span style="color:#bbb"> </span>CHARSET<span style="color:#000;font-weight:bold">=</span>utf8mb4;<span style="color:#bbb">
</span></span></span><span style="display:flex;"><span><span style="color:#bbb">
</span></span></span><span style="display:flex;"><span><span style="color:#bbb"></span><span style="color:#000;font-weight:bold">drop</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">procedure</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">if</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">exists</span><span style="color:#bbb"> </span>insert_tbl_order_info;<span style="color:#bbb">
</span></span></span><span style="display:flex;"><span><span style="color:#bbb"></span><span style="color:#000;font-weight:bold">delimiter</span><span style="color:#bbb"> </span>;;<span style="color:#bbb">
</span></span></span><span style="display:flex;"><span><span style="color:#bbb"></span><span style="color:#000;font-weight:bold">create</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">procedure</span><span style="color:#bbb"> </span>insert_tbl_order_info()<span style="color:#bbb">
</span></span></span><span style="display:flex;"><span><span style="color:#bbb"></span><span style="color:#000;font-weight:bold">begin</span><span style="color:#bbb">
</span></span></span><span style="display:flex;"><span><span style="color:#bbb">    </span><span style="color:#000;font-weight:bold">declare</span><span style="color:#bbb"> </span>i<span style="color:#bbb"> </span><span style="color:#0086b3">int</span>;<span style="color:#bbb">
</span></span></span><span style="display:flex;"><span><span style="color:#bbb">    </span><span style="color:#000;font-weight:bold">set</span><span style="color:#bbb"> </span>i<span style="color:#000;font-weight:bold">=</span><span style="color:#099">1</span>;<span style="color:#bbb">
</span></span></span><span style="display:flex;"><span><span style="color:#bbb">    </span>while(i<span style="color:#000;font-weight:bold">&lt;=</span><span style="color:#099">1000</span>)<span style="color:#000;font-weight:bold">do</span><span style="color:#bbb">
</span></span></span><span style="display:flex;"><span><span style="color:#bbb">    </span><span style="color:#000;font-weight:bold">insert</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">into</span><span style="color:#bbb"> </span>tbl_order_info(amount,<span style="color:#bbb"> </span>user_no)<span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">values</span>(i,<span style="color:#bbb"> </span>i<span style="color:#000;font-weight:bold">+</span><span style="color:#099">1</span>);<span style="color:#bbb">
</span></span></span><span style="display:flex;"><span><span style="color:#bbb">    </span><span style="color:#000;font-weight:bold">set</span><span style="color:#bbb"> </span>i<span style="color:#000;font-weight:bold">=</span>i<span style="color:#000;font-weight:bold">+</span><span style="color:#099">1</span>;<span style="color:#bbb">
</span></span></span><span style="display:flex;"><span><span style="color:#bbb">        </span><span style="color:#000;font-weight:bold">end</span><span style="color:#bbb"> </span>while;<span style="color:#bbb">
</span></span></span><span style="display:flex;"><span><span style="color:#bbb"></span><span style="color:#000;font-weight:bold">end</span>;;<span style="color:#bbb">
</span></span></span><span style="display:flex;"><span><span style="color:#bbb"></span><span style="color:#000;font-weight:bold">delimiter</span><span style="color:#bbb"> </span>;<span style="color:#bbb">
</span></span></span><span style="display:flex;"><span><span style="color:#bbb"></span><span style="color:#000;font-weight:bold">call</span><span style="color:#bbb"> </span>insert_tbl_order_info();<span style="color:#bbb">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h2 id="执行explain">执行explain</h2>
<p>查询<code>user_no</code>为999的订单信息.查看此sql的执行计划.</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4;">
<table style="border-spacing:0;padding:0;margin:0;border:0;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre tabindex="0" style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">2
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre tabindex="0" style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-sql" data-lang="sql"><span style="display:flex;"><span><span style="color:#000;font-weight:bold">explain</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">select</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">*</span><span style="color:#bbb"> </span><span style="color:#000;font-weight:bold">from</span><span style="color:#bbb"> </span>tbl_order_info<span style="color:#bbb"> 
</span></span></span><span style="display:flex;"><span><span style="color:#bbb"></span><span style="color:#000;font-weight:bold">where</span><span style="color:#bbb"> </span>user_no<span style="color:#000;font-weight:bold">=</span><span style="color:#d14">&#39;999&#39;</span>;<span style="color:#bbb"> 
</span></span></span></code></pre></td></tr></table>
</div>
</div><p>输出:</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4;">
<table style="border-spacing:0;padding:0;margin:0;border:0;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre tabindex="0" style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">2
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">3
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">4
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">5
</span><span style="white-space:pre;-webkit-user-select:none;user-select:none;margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">6
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre tabindex="0" style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-text" data-lang="text"><span style="display:flex;"><span>+----+-------------+----------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
</span></span><span style="display:flex;"><span>| id | select_type | table          | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
</span></span><span style="display:flex;"><span>+----+-------------+----------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
</span></span><span style="display:flex;"><span>|  1 | SIMPLE      | tbl_order_info | NULL       | ref  | idx_user_no   | idx_user_no | 131     | const |    1 |   100.00 | NULL  |
</span></span><span style="display:flex;"><span>+----+-------------+----------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
</span></span><span style="display:flex;"><span>1 row in set, 1 warning (0.00 sec)
</span></span></code></pre></td></tr></table>
</div>
</div><p>执行计划显示,select_type:是简单查询,key:查询使用idx_user_no索引,type:基于普通索引的等值查询,ref:和索引比较的是常量.</p>
<h2 id="explain输出字段含义">explain输出字段含义</h2>
<table>
<thead>
<tr>
<th>列名</th>
<th>解释</th>
</tr>
</thead>
<tbody>
<tr>
<td>id</td>
<td>查询编号</td>
</tr>
<tr>
<td>select_type</td>
<td>查询类型:显示本行是简单还是复杂查询</td>
</tr>
<tr>
<td>table</td>
<td>涉及到的表</td>
</tr>
<tr>
<td>partitions</td>
<td>匹配的分区:查询将匹配记录所在的分区.仅当使用 partition 关键字时才显示该列。对于非分区表,该值为 NULL。</td>
</tr>
<tr>
<td>type</td>
<td>本次查询的表连接类型</td>
</tr>
<tr>
<td>possible_keys</td>
<td>可能选择的索引</td>
</tr>
<tr>
<td>key</td>
<td>实际选择的索引</td>
</tr>
<tr>
<td>key_len</td>
<td>被选择的索引长度:一般用于判断联合索引有多少列被选择了</td>
</tr>
<tr>
<td>ref</td>
<td>与索引比较的列</td>
</tr>
<tr>
<td>filtered</td>
<td>按条件筛选的行的百分比</td>
</tr>
<tr>
<td>Extra</td>
<td>附加信息</td>
</tr>
</tbody>
</table>
<h2 id="explain中select_type">explain中select_type</h2>
<table>
<thead>
<tr>
<th>select的值</th>
<th>解释</th>
</tr>
</thead>
<tbody>
<tr>
<td>SIMPLE</td>
<td>简单查询 (不使用关联查询或子查询)</td>
</tr>
<tr>
<td>PRIMARY</td>
<td>如果包含关联查询或者子查询,则最外层的查询部分标记为 primary</td>
</tr>
<tr>
<td>UNION</td>
<td>联合查询中第二个及后面的查询</td>
</tr>
<tr>
<td>DEPENDENT UNION</td>
<td>满足依赖外部的关联查询中第二个及以后的查询</td>
</tr>
<tr>
<td>UNION RESULT</td>
<td>联合查询的结果</td>
</tr>
<tr>
<td>SUBQUERY</td>
<td>子查询中的第一个查询</td>
</tr>
<tr>
<td>DEPENDENT SUBQUERY</td>
<td>子查询中的第一个查询,并且依赖外部查询</td>
</tr>
<tr>
<td>DERIVED</td>
<td>用到派生表的查询</td>
</tr>
<tr>
<td>MATERIALIZED</td>
<td>被物化的子查询</td>
</tr>
<tr>
<td>UNCACHEABLE SUBQUERY</td>
<td>一个子查询的结果不能被缓存,必须重新评估外层查询的每一行</td>
</tr>
<tr>
<td>UNCACHEABLE UNION</td>
<td>关联查询第二个或后面的语句属于不可缓存的子查询</td>
</tr>
</tbody>
</table>
<h2 id="explain中type">explain中type</h2>
<table>
<thead>
<tr>
<th>type的值</th>
<th>解释</th>
</tr>
</thead>
<tbody>
<tr>
<td>system</td>
<td>查询对象表只有一行数据,且只能用于 MyISAM 和 Memory 引擎的表,这是最好的情况</td>
</tr>
<tr>
<td>const</td>
<td>基于主键或唯一索引查询,最多返回一条结果</td>
</tr>
<tr>
<td>eq_ref</td>
<td>表连接时基于主键或非 NULL 的唯一索引完成扫描</td>
</tr>
<tr>
<td>ref</td>
<td>基于普通索引的等值查询,或者表间等值连接</td>
</tr>
<tr>
<td>fulltext</td>
<td>全文检索</td>
</tr>
<tr>
<td>ref_or_null</td>
<td>表连接类型是 ref,但进行扫描的索引列中可能包含 NULL 值</td>
</tr>
<tr>
<td>index_merge</td>
<td>利用多个索引</td>
</tr>
<tr>
<td>unique_subquery</td>
<td>子查询中使用唯一索引</td>
</tr>
<tr>
<td>index_subquery</td>
<td>子查询中使用普通索引</td>
</tr>
<tr>
<td>range</td>
<td>利用索引进行范围查询</td>
</tr>
<tr>
<td>index</td>
<td>全索引扫描</td>
</tr>
<tr>
<td>ALL</td>
<td>全表扫描</td>
</tr>
<tr>
<td>上表的这些情况,查询性能从上到下依次是最好到最差.</td>
<td></td>
</tr>
</tbody>
</table>
<h2 id="explain中extra">explain中Extra</h2>
<table>
<thead>
<tr>
<th>Extra常见的值</th>
<th>解释</th>
</tr>
</thead>
<tbody>
<tr>
<td>Using filesort</td>
<td>将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序</td>
</tr>
<tr>
<td>Using temporary</td>
<td>需要创建一个临时表来存储结构，通常发生对没有索引的列进行 GROUP BY 时</td>
</tr>
<tr>
<td>Using index</td>
<td>使用覆盖索引</td>
</tr>
<tr>
<td>Using where</td>
<td>使用 where 语句来处理结果</td>
</tr>
<tr>
<td>Impossible WHERE</td>
<td>对 where 子句判断的结果总是 false 而不能选择任何数据</td>
</tr>
<tr>
<td>Using join buffer (Block Nested Loop)</td>
<td>关联查询中，被驱动表的关联字段没索引</td>
</tr>
<tr>
<td>Using index condition</td>
<td>先条件过滤索引，再查数据</td>
</tr>
<tr>
<td>Select tables optimized away</td>
<td>使用某些聚合函数（比如 max、min）来访问存在索引的某个字段</td>
</tr>
</tbody>
</table>
<h2 id="sql分析的其他方式">sql分析的其他方式</h2>
<p><code>show profile</code>,<code>trace</code>等工具也能分析我们的sql.<code>explain</code>是查看执行计划,<code>show profile</code>是查看sql执行过程中各部分的耗时.
<code>trace</code>是查看sql执行过程中的详细信息,使用了哪些索引,为什么使用,为何选取什么样子的排序方式等.</p>
        </div>

        
<div class="post-archive">
    <ul class="post-copyright">
        <li><strong>原文作者：</strong><a rel="author" href="http://kklt1996.gitee.io/blog/">lsy</a></li>
        <li style="word-break:break-all"><strong>原文链接：</strong><a href="http://kklt1996.gitee.io/blog/post/mysql/2020-04-06-%E4%BD%BF%E7%94%A8explain%E5%88%86%E6%9E%90sql%E8%AF%AD%E5%8F%A5/">http://kklt1996.gitee.io/blog/post/mysql/2020-04-06-%E4%BD%BF%E7%94%A8explain%E5%88%86%E6%9E%90sql%E8%AF%AD%E5%8F%A5/</a></li>
        <li><strong>版权声明：</strong>本作品采用<a rel="license" href="https://creativecommons.org/licenses/by-nc-nd/4.0/">知识共享署名-非商业性使用-禁止演绎 4.0 国际许可协议</a>进行许可，非商业转载请注明出处（作者，原文链接），商业转载请联系作者获得授权。</li>
    </ul>
</div>
<br/>



        

<div class="post-archive">
    <h2>See Also</h2>
    <ul class="listing">
        
        <li><a href="/blog/post/mysql/2020-04-06-mysql%E7%B4%A2%E5%BC%95%E5%A4%B1%E6%95%88%E7%9A%84%E6%83%85%E5%86%B5/">mysql索引失效的情况</a></li>
        
        <li><a href="/blog/post/mysql/2020-04-06-docker%E5%AE%89%E8%A3%85mysql/">docker安装mysql实验环境</a></li>
        
        <li><a href="/blog/post/elasticsearch/2020-03-30-elasticsearch-helloworld/">认识 Elasticsearch</a></li>
        
        <li><a href="/blog/post/data-structure/2020-03-29-%E6%95%B0%E7%BB%84/">数组</a></li>
        
        <li><a href="/blog/post/springboot/springboot_analysis/">Springboot工作机制</a></li>
        
    </ul>
</div>


        <div class="post-meta meta-tags">
            
            没有标签
            
        </div>
    </article>
    
    

    
    
</div>

                </div>

                <div id="secondary">
    <section class="widget">
        <form id="search" action='http://kklt1996.gitee.io/blog/search/' method="get" accept-charset="utf-8" target="_blank" _lpchecked="1">
      
      <input type="text" name="q" maxlength="20" placeholder="Search">
      <input type="hidden" name="sitesearch" value="http://kklt1996.gitee.io/blog/">
      <button type="submit" class="submit icon-search"></button>
</form>
    </section>
    
    <section class="widget">
        <h3 class="widget-title">最近文章</h3>
<ul class="widget-list">
    
    <li>
        <a href="http://kklt1996.gitee.io/blog/post/golang/2023-10-31-go%E7%94%9F%E4%BA%A7%E6%B6%88%E8%B4%B9%E6%A8%A1%E5%9E%8B/" title="golang中的生产消费模型">golang中的生产消费模型</a>
    </li>
    
    <li>
        <a href="http://kklt1996.gitee.io/blog/post/golang/2023-10-31-go%E5%8D%8F%E7%A8%8B%E9%97%B4%E9%80%9A%E4%BF%A1/" title="golang协程间通信">golang协程间通信</a>
    </li>
    
    <li>
        <a href="http://kklt1996.gitee.io/blog/post/java/2020-05-26-%E5%8D%95%E4%BE%8B%E6%A8%A1%E5%BC%8F/" title="单利模式发布对象">单利模式发布对象</a>
    </li>
    
    <li>
        <a href="http://kklt1996.gitee.io/blog/post/redis/2020-05-20-redis%E5%AE%9E%E7%8E%B0%E5%88%86%E5%B8%83%E5%BC%8F%E9%94%81/" title="Redis实现分布式锁">Redis实现分布式锁</a>
    </li>
    
    <li>
        <a href="http://kklt1996.gitee.io/blog/post/java/2020-05-04-%E5%8E%9F%E5%AD%90%E6%80%A7-%E5%8F%AF%E8%A7%81%E6%80%A7-%E6%9C%89%E5%BA%8F%E6%80%A7/" title="原子性 可见性 有序性">原子性 可见性 有序性</a>
    </li>
    
    <li>
        <a href="http://kklt1996.gitee.io/blog/post/java/2020-04-21-juc-%E7%BA%BF%E7%A8%8B%E5%AE%89%E5%85%A8%E7%9A%84%E7%B1%BB/" title="线程安全的类">线程安全的类</a>
    </li>
    
    <li>
        <a href="http://kklt1996.gitee.io/blog/post/java/2020-04-21-java%E5%86%85%E5%AD%98%E6%A8%A1%E5%9E%8B/" title="java内存模型">java内存模型</a>
    </li>
    
    <li>
        <a href="http://kklt1996.gitee.io/blog/post/mybatis/2020-04-12-mybatis%E6%89%B9%E9%87%8F%E6%8F%92%E5%85%A5%E8%BF%94%E5%9B%9E%E8%87%AA%E5%A2%9E%E4%B8%BB%E9%94%AE/" title="mybatis批量插入返回自增主键">mybatis批量插入返回自增主键</a>
    </li>
    
    <li>
        <a href="http://kklt1996.gitee.io/blog/post/java/2020-04-14-juc-%E7%BA%BF%E7%A8%8B%E6%B1%A0/" title="java中的线程池">java中的线程池</a>
    </li>
    
    <li>
        <a href="http://kklt1996.gitee.io/blog/post/java/2020-04-14-juc-%E4%BF%A1%E5%8F%B7%E9%87%8F/" title="Semaphore信号量">Semaphore信号量</a>
    </li>
    
</ul>
    </section>

    

    <section class="widget">
        <h3 class="widget-title">分类</h3>
<ul class="widget-list">
    
    <li><a href="http://kklt1996.gitee.io/blog/categories/elasticsearch/">elasticsearch (1)</a></li>
    
    <li><a href="http://kklt1996.gitee.io/blog/categories/golang/">golang (2)</a></li>
    
    <li><a href="http://kklt1996.gitee.io/blog/categories/java%E5%B9%B6%E5%8F%91%E7%BC%96%E7%A8%8B/">java并发编程 (6)</a></li>
    
    <li><a href="http://kklt1996.gitee.io/blog/categories/json/">json (1)</a></li>
    
    <li><a href="http://kklt1996.gitee.io/blog/categories/mybatis/">mybatis (1)</a></li>
    
    <li><a href="http://kklt1996.gitee.io/blog/categories/mysql/">mysql (3)</a></li>
    
    <li><a href="http://kklt1996.gitee.io/blog/categories/redis/">redis (1)</a></li>
    
    <li><a href="http://kklt1996.gitee.io/blog/categories/springBoot/">springBoot (11)</a></li>
    
    <li><a href="http://kklt1996.gitee.io/blog/categories/ubuntu/">ubuntu (3)</a></li>
    
    <li><a href="http://kklt1996.gitee.io/blog/categories/%E6%95%B0%E6%8D%AE%E7%BB%93%E6%9E%84%E5%92%8C%E7%AE%97%E6%B3%95/">数据结构和算法 (1)</a></li>
    
    <li><a href="http://kklt1996.gitee.io/blog/categories/%E6%B7%B1%E5%BA%A6%E5%AD%A6%E4%B9%A0/">深度学习 (1)</a></li>
    
</ul>
    </section>

    <section class="widget">
        <h3 class="widget-title">标签</h3>
<div class="tagcloud">
    
    <a href="http://kklt1996.gitee.io/blog/tags/aspect/">aspect</a>
    
    <a href="http://kklt1996.gitee.io/blog/tags/devTool/">devTool</a>
    
    <a href="http://kklt1996.gitee.io/blog/tags/docker/">docker</a>
    
    <a href="http://kklt1996.gitee.io/blog/tags/filter/">filter</a>
    
    <a href="http://kklt1996.gitee.io/blog/tags/hibernate/">hibernate</a>
    
    <a href="http://kklt1996.gitee.io/blog/tags/idea/">idea</a>
    
    <a href="http://kklt1996.gitee.io/blog/tags/intercept/">intercept</a>
    
    <a href="http://kklt1996.gitee.io/blog/tags/interceptor/">interceptor</a>
    
    <a href="http://kklt1996.gitee.io/blog/tags/jackjson/">jackjson</a>
    
    <a href="http://kklt1996.gitee.io/blog/tags/resultful/">resultful</a>
    
    <a href="http://kklt1996.gitee.io/blog/tags/springTest/">springTest</a>
    
    <a href="http://kklt1996.gitee.io/blog/tags/spring%E8%87%AA%E5%AE%9A%E4%B9%89%E5%BC%82%E5%B8%B8%E5%A4%84%E7%90%86/">spring自定义异常处理</a>
    
    <a href="http://kklt1996.gitee.io/blog/tags/swagger/">swagger</a>
    
    <a href="http://kklt1996.gitee.io/blog/tags/swap/">swap</a>
    
    <a href="http://kklt1996.gitee.io/blog/tags/validator/">validator</a>
    
    <a href="http://kklt1996.gitee.io/blog/tags/%E5%8A%A8%E6%80%81%E6%95%B0%E6%8D%AE%E6%BA%90/">动态数据源</a>
    
    <a href="http://kklt1996.gitee.io/blog/tags/%E6%B7%B1%E5%BA%A6%E5%AD%A6%E4%B9%A0/">深度学习</a>
    
    <a href="http://kklt1996.gitee.io/blog/tags/%E9%98%B2%E7%81%AB%E5%A2%99/">防火墙</a>
    
</div>
    </section>

    
<section class="widget">
    <h3 class="widget-title">友情链接</h3>
    <ul class="widget-list">
        
        <li>
            <a target="_blank" href="https://gitee.com/kklt1996" title="码云地址">码云地址</a>
        </li>
        
    </ul>
</section>


    <section class="widget">
        <h3 class="widget-title">其它</h3>
        <ul class="widget-list">
            <li><a href="http://kklt1996.gitee.io/blog/index.xml">文章 RSS</a></li>
        </ul>
    </section>
</div>
            </div>
        </div>
    </div>

    

    <footer id="footer">
    <div class="container">
        &copy; 2023 <a href="http://kklt1996.gitee.io/blog/">lsy&#39;blog By lsy</a>.
        Powered by <a rel="nofollow noreferer noopener" href="https://gohugo.io" target="_blank">Hugo</a>.
        <a href="https://www.flysnow.org/" target="_blank">Theme</a> based on <a href="https://github.com/flysnow-org/maupassant-hugo" target="_blank">maupassant</a>.
        
    </div>
</footer>


    
    <script type="text/javascript">
        window.MathJax = {
            tex2jax: {
                inlineMath: [['$', '$']],
                processEscapes: true
                }
            };
    </script>
    <script src='https://cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.5/MathJax.js?config=TeX-MML-AM_CHTML' async></script>


<a id="rocket" href="#top"></a>
<script type="text/javascript" src='/blog/js/totop.js?v=0.0.0' async=""></script>



    <script type="text/javascript" src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js" async></script>




    <script src='/js/douban.js'></script>

</body>

</html>